---
title: "Acala / Karura Token Dashboard"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}
# web3.py
# eth95.dev
library(knitr)
knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)

library(dygraphs)
library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)
library(subscanr)
library(ghql)
x <- GraphqlClient$new()

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

library(reticulate)
```

```{python, include=FALSE}
from substrateinterface import SubstrateInterface

from datetime import datetime
import pandas as pd
import numpy as np
from datetime import date
from pprint import pprint

Karura = 'wss://karura.polkawallet.io'
Acala = 'wss://acala-rpc-0.aca-api.network'
            
def get_accounts(network):
    if network == "Acala":
        url = Acala
    else:
        url = Karura
        
    fname = f"{network}_accounts.csv"
    substrate = SubstrateInterface(url)
    result = substrate.query_map('System', 'Account')

    data = []
    for res in result:
        # res = result[0]    
        # print(f"Free balance of account '{account.value}': {account_info.value['data']['free']}")
        acct = res[0].value
        free = res[1].value['data']['free']
        outi = {"account": acct, "free": free} 
        data.append(outi) 
        
    out = pd.DataFrame(data)           
    out.to_csv(fname, mode = "w", index = False, header = True)

# get_accounts("Acala")
# get_accounts("Karura")

```

```{r tokens, cache = TRUE, include=FALSE}

# accounts_sq <- getAccounts_acala('Acala', window = 1, filter = '', endpage = 2000)
# fwrite(accounts_sq, file = "accounts_sq.csv")
# accounts_ss <- get_subscan_accounts('Acala', nobs = 2000000, start_page = 1)
# fwrite(accounts_ss, file = "accounts_ss.csv")

# karura_accounts_sq <- getAccounts_acala('Karura', window = 1, filter = '', endpage = 2000)
# fwrite(karura_accounts_sq, file = "karura_accounts_sq.csv")
# karura_accounts_ss <- get_subscan_accounts('Karura', nobs = 2000000, start_page = 1)
# fwrite(karura_accounts_ss, file = "karura_accounts_ss.csv")

# Acala data
accounts_api <- fread(file = "~/Python_HOME/Acala_accounts.csv")
accounts_api[, api := 1]

accounts_sq <- fread(file = "~/R_HOME/subscanr/accounts_sq.csv")
accounts_sq[, subquery := 1]

accounts_ss <- fread(file = "~/R_HOME/subscanr/Acala_accounts2.csv", fill = TRUE)
accounts_ss[, subscan := 1]
accounts_ss <- accounts_ss[list.address != '', .(list.address, list.balance, subscan)] %>%
  setnames(c("id", "balance", "subscan"))

both <- merge(accounts_sq, accounts_ss, by = "id", all = TRUE)
both <- merge(both, 
              accounts_api,
              by.x = "id",
              by.y = "account",
              all = TRUE)

# Karura data
karura_accounts_api <- fread(file = "~/Python_HOME/Karura_accounts.csv")
karura_accounts_api[, api := 1]

karura_accounts_sq <- fread(file = "~/R_HOME/subscanr/karura_accounts_sq.csv")
karura_accounts_sq[, subquery := 1]

karura_accounts_ss <- fread(file = "~/R_HOME/subscanr/Karura_accounts2.csv", fill = TRUE)
karura_accounts_ss[, subscan := 1]

karura_accounts_ss <- karura_accounts_ss[list.address != '', .(list.address, list.balance, subscan)] %>%
  setnames(c("id", "balance", "subscan"))

karura_both <- merge(karura_accounts_sq, karura_accounts_ss, by = "id", all = TRUE)
karura_both <- merge(karura_both, 
                     karura_accounts_api[free > 0],
                     by.x = "id",
                     by.y = "account",
                     all = TRUE)

```

# Acala {.tabset}

Row
----

### Acala Accounts Comparison

* Acala-tokens Subquery accounts: `r both[subquery == 1, .N]`

* Subscan accounts: `r both[subscan == 1, .N]`

* API accounts (ignoring free balance): `r both[api == 1, .N]`

* API accounts (free balance > 0): `r both[free > 0 & api == 1, .N]`

* Overlap between Subquery and Subscan: `r both[subquery == 1 & subscan == 1, .N]`

* Overlap between Subscan and API: `r both[api == 1 & subscan == 1, .N]`

From the above data it appears that the number of accounts being reported by Subscan is much closer to the number from the API.  For the purposes of the stats page, getting the number of accounts from the API would take too long, so the quickest way is from Subscan.  The acala-subql project is vastly under-reporting the number of accounts.  The other issue with the acala-subql project is that all the related fields are NA for all observations, such as *txCount* and *createAtBlockId*.

Row
----

### `r both[free > 0 & api == 1 & is.na(subscan), .N]` Accounts in API that are missing from Subscan

Showing first 50 observations...
```{r acala1}

knitr::kable(head(both[free > 0 & api == 1 & is.na(subscan)], 50), escape = FALSE) %>%
  kable_styling()

```


Row
----

### `r both[is.na(api) & subscan == 1, .N]` Accounts in Subscan that are missing from the API

Showing first 50 observations...
```{r acala2}

knitr::kable(head(both[is.na(api) & subscan == 1], 50), escape = FALSE) %>%
  kable_styling()

```

# Karura {.tabset}

Row
----

### Karura Accounts Comparison

* Karura-tokens Subquery accounts: `r karura_both[subquery == 1, .N]`

* Subscan accounts: `r karura_both[subscan == 1, .N]`

* API accounts (ignoring free balance): `r karura_both[api == 1, .N]`

* API accounts (free balance > 0): `r karura_both[free > 0 & api == 1, .N]`

* Overlap between Subquery and API: `r karura_both[subquery == 1 & api == 1, .N]`

* Overlap between Subscan and API: `r karura_both[api == 1 & subscan == 1, .N]`

Similar to Acala Network, the acala-subql project for Karura Network seems to have a poor overlap with the accounts reported by the API, whereas Subscan has 100% overlap (all the accounts reported by the API are also reported by Subscan -- Subscan is showing some extra accounts as well!).  

Row
----

### `r karura_both[free > 0 & api == 1 & is.na(subscan), .N]` Accounts in API that are missing from Subscan

Showing first 50 observations...
```{r karura1}

knitr::kable(head(karura_both[free > 0 & api == 1 & is.na(subscan)], 50), escape = FALSE) %>%
  kable_styling()

```


Row
----

### `r karura_both[is.na(api) & subscan == 1, .N]` Accounts in Subscan that are missing from the API

Showing first 50 observations...
```{r karura2}

knitr::kable(head(karura_both[is.na(api) & subscan == 1], 50), escape = FALSE) %>%
  kable_styling()

```